package com.ayf.payment.game.api.utils;

import com.ayf.payment.game.api.dto.platform.MerchantCashOutDTO;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.List;

/**
 *
 *
 * @author wwl
 * @date 2020/3/12 19:40
 */
public class ExportFileUtil {

    public static void main(String[] args) throws Exception {
        ExportFileUtil efu=new ExportFileUtil();
       // efu.exportExcel(null);
    }

//List<MerchantCashOutDTO>  getCashOutByBatchList(MerchantCashOutPageCriteria selectCriteria)
    public static void exportExcel(HttpServletResponse response, List<MerchantCashOutDTO> list, String batchNumber) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("碳排放月报表(纳入交易)");
        createTitle(workbook,sheet,batchNumber);
        for(int i=0,n=list.size();i<n;i++) {
            HSSFRow row = sheet.createRow(i+2);
            row.setHeightInPoints(18);
            row.createCell(0).setCellValue(list.get(i).getTradeNumber());
            row.createCell(1).setCellValue(list.get(i).getBankCity());
            row.createCell(2).setCellValue(list.get(i).getBankBranchName());
            row.createCell(3).setCellValue(list.get(i).getBankName());
            row.createCell(4).setCellValue("C");
            row.createCell(5).setCellValue(list.get(i).getAccountName());
            row.createCell(6).setCellValue(list.get(i).getCardNo());
            row.createCell(7).setCellValue(list.get(i).getActualAmount()+"");
            row.createCell(8).setCellValue("付款");
        }
        String fileName = batchNumber+".xls";

        //生成excel文件
      //  buildExcelFile(fileName, workbook);

//        //浏览器下载excel
       buildExcelDocument(fileName,workbook,response);
    }

    private static String titleStr="注意：单个文件记录数不能超过20000条；\n\r" +
            "银行名称请填写完整名称：如“工商银行”；\n\r" +
            "当银行名称在“工商银行、建设银行、农业银行、招商银行、交通银行、兴业银行、广东发展银行、中信银行、深圳发展银行、浦东发展银行”中时，省份、城市、开户支行名称三列可以不填。";

    //创建表头
    private static void createTitle(HSSFWorkbook workbook,HSSFSheet sheet,String batchNumber){
        //合并单元格
        //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
        CellRangeAddress region1 = new CellRangeAddress(0, 0, 2, 8);
        sheet.addMergedRegion(region1);
        HSSFRow row = sheet.createRow(0);
        //设置行高
        row.setHeightInPoints(35);
        //设置列宽度
        sheet.autoSizeColumn(1);
        //设置为居中加粗
        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        font.setColor(Font.COLOR_RED);
        font.setFontHeightInPoints((short) 9);
        style.setFont(font);
        style.setWrapText(true);
//        style.setAlignment((short) HorizontalAlignment.LEFT.ordinal());
//        style.setVerticalAlignment((short)VerticalAlignment.CENTER.ordinal());
        style.setFillForegroundColor((short) 30);

        HSSFCellStyle style1 = workbook.createCellStyle();
        HSSFFont font1 = workbook.createFont();
        font1.setBold(true);
        style1.setFont(font1);
//        style1.setAlignment((short) HorizontalAlignment.CENTER.ordinal());
//        style1.setVerticalAlignment((short)VerticalAlignment.CENTER.ordinal());
        style1.setFillForegroundColor((short) 30);

        HSSFCell cell;
        cell = row.createCell(0);
        cell.setCellValue("批次号");
        cell.setCellStyle(style1);
        cell = row.createCell(1);
        cell.setCellValue(batchNumber);
        cell.setCellStyle(style1);

        cell = row.createCell(2);
        cell.setCellValue(titleStr);
        cell.setCellStyle(style);

        HSSFRow row1 = sheet.createRow(1);
        //设置行高
        row1.setHeightInPoints(18);
        //设置列宽度
        sheet.setDefaultColumnWidth(25);
        sheet.setColumnWidth(5,32*256);

        HSSFCell cell2;
        cell2 = row1.createCell(0);
        cell2.setCellValue("商户流水号");
        cell2.setCellStyle(style1);

        cell2 = row1.createCell(1);
        cell2.setCellValue("收款省/直辖市");
        cell2.setCellStyle(style1);

        cell2 = row1.createCell(2);
        cell2.setCellValue("收款银行支行");
        cell2.setCellStyle(style1);
        cell2 = row1.createCell(3);
        cell2.setCellValue("银行名称");
        cell2.setCellStyle(style1);
        cell2 = row1.createCell(4);
        cell2.setCellValue("收款人账户类型（C为个人B为企业）");
        cell2.setCellStyle(style1);
        cell2 = row1.createCell(5);
        cell2.setCellValue("收款人户名");
        cell2.setCellStyle(style1);
        cell2 = row1.createCell(6);
        cell2.setCellValue("收款方银行账号");
        cell2.setCellStyle(style1);
        cell2 = row1.createCell(7);
        cell2.setCellValue("付款金额（元）");
        cell2.setCellStyle(style1);
        cell2 = row1.createCell(8);
        cell2.setCellValue("备注");
        cell2.setCellStyle(style1);

    }
    //生成excel文件
    public void buildExcelFile(String filename,HSSFWorkbook workbook) throws Exception{
        FileOutputStream fos = new FileOutputStream(filename);
        workbook.write(fos);
        fos.flush();
        fos.close();
    }

    //浏览器下载excel
    public static void buildExcelDocument(String filename,HSSFWorkbook workbook,HttpServletResponse response) throws Exception{
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(filename, "utf-8"));
        OutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.flush();
        outputStream.close();
    }
}
